libname red "/.../F5500/microdata";
libname blue "/.../data"; 

%macro brlib(yy);
	libname br&yy "/.../ssl/&yy";
	libname f55b&yy "/.../f55bf/&yy";
%mend brlib;

%macro ldblib(yy);
	libname lbd&yy "/.../lbd/&yy";
%mend ldblib;

%brlib(1996); %brlib(1997); %brlib(1998); %brlib(1999);
%brlib(2000); %brlib(2001); %brlib(2002); %brlib(2003);
%brlib(2004); %brlib(2005); %brlib(2006); %brlib(2007);
%brlib(2008); %brlib(2009); %brlib(2010); %brlib(2011);
%brlib(2012); %brlib(2013); %brlib(2014);

%ldblib(1996); %ldblib(1997); %ldblib(1998); %ldblib(1999);
%ldblib(2000); %ldblib(2001); %ldblib(2002); %ldblib(2003);
%ldblib(2004); %ldblib(2005); %ldblib(2006); %ldblib(2007);
%ldblib(2008); %ldblib(2009); %ldblib(2010); %ldblib(2011);
%ldblib(2012); %ldblib(2013); %ldblib(2014);

%include "/.../programs/includes/format_source_2000.sas";
%include "/.../programs/includes/format_source_2001.sas";
%include "/.../programs/includes/format_source_2002.sas";
%include "/.../programs/includes/format_source_2004.sas";
%include "/.../programs/includes/format_source_2005.sas";
%include "/.../programs/includes/format_source_2014.sas";
%include "/.../programs/includes/read_schedsb.sas"; 
%include "/.../programs/includes/addschedbdat.sas"; 
%include "/.../programs/includes/dbxwalktobr.sas"; 

/**********************************************************************
* Author : Dhiren Patki
*
* This program links the 5500 derived data to the 5500BR bridge
* and creates a panel of cleaned DB plan records. 
* The BR-linked panel is used to match plans to employers in the LEHD.
***********************************************************************/

/*Add actuarial information to DB pension plan records
* 1996-2008 is based on schedule B. 2009-2014 is based on 
* schedule SB. 2008 data is largely missing from DoL 
* records unless filed in another year. 
* Interpolate 2008 missing values in a subsequent step
* The output of each call is the file db&yyyy*/

%addschbdat(1996); %addschbdat(1997); %addschbdat(1998); %addschbdat(1999);
%addschbdat(2000); %addschbdat(2001); %addschbdat(2002); %addschbdat(2003);
%addschbdat(2004); %addschbdat(2005); %addschbdat(2006); %addschbdat(2007);
%addschbdat(2008); %addschbdat0914(2009); %addschbdat0914(2010); 
%addschbdat0914(2011); %addschbdat0914(2012); %addschbdat0914(2013); 
%addschbdat0914(2014);

/*Next, xwalk DB plan records to the BR and obtain firm-level information
* the output of each call is blue.db&yy._brx_fin*/

%dbxwalktobr(1996); %dbxwalktobr(1997); %dbxwalktobr(1998); %dbxwalktobr(1999);
%dbxwalktobr(2000); %dbxwalktobr(2001); %dbxwalktobr(2002); %dbxwalktobr(2003);
%dbxwalktobr(2004); %dbxwalktobr(2005); %dbxwalktobr(2006); %dbxwalktobr(2007);
%dbxwalktobr(2008); %dbxwalktobr(2009); %dbxwalktobr(2010); %dbxwalktobr(2011);
%dbxwalktobr1214(2012); %dbxwalktobr1214(2013); %dbxwalktobr1214(2014);

/*Stack the disclosure output files*/
data discl_f55br9614;	
     set discl_f55br1996 discl_f55br1997 discl_f55br1998
     discl_f55br1999 discl_f55br2000 discl_f55br2001
     discl_f55br2002 discl_f55br2003 discl_f55br2004 
     discl_f55br2005 discl_f55br2006 discl_f55br2007
     discl_f55br2008 discl_f55br2009 discl_f55br2010
     discl_f55br2011 discl_f55br2012 discl_f55br2013
     discl_f55br2014;
run;

/*Output for analysis in stata*/
proc export data = discl_f55br9614
	outfile = "/.../data/f55brmtch_cratio.csv"
	dbms = csv replace;
run;

/*Clean up the working directory*/
proc datasets lib=work nolist kill;
quit;
run;

/**********************************************************************
* Create a panel of DB plans
**********************************************************************/

data db_brx_fin0 (drop=collective_bargain_ind asset_return cost_method_code);
	set blue.db1996_brx_fin
	    blue.db1997_brx_fin 
	    blue.db1998_brx_fin 
	    blue.db1999_brx_fin 
	    blue.db2000_brx_fin 
	    blue.db2001_brx_fin 
	    blue.db2002_brx_fin 
	    blue.db2003_brx_fin 
	    blue.db2004_brx_fin 
	    blue.db2005_brx_fin
	    blue.db2006_brx_fin
	    blue.db2007_brx_fin
	    blue.db2008_brx_fin
	    blue.db2009_brx_fin
	    blue.db2010_brx_fin
	    blue.db2011_brx_fin
	    blue.db2012_brx_fin
	    blue.db2013_brx_fin
	    blue.db2014_brx_fin;
	    union_plan=/*redacted*/;
	    if collective_bargain_ind=/*redacted*/ then union_plan=/*redacted*/;
	    sic_2dig_br = /*redacted*/;
	    label 
	    union_plan = "Collectively bargained plan"
	    firmage = "Firm age from LBD"
	    sic_2dig_br= "2 digit SIC code";
run;

/* Delete any plan filings with identical filing IDs*/

proc sort data = db_brx_fin0 nodupkey;
	by filing_id_char plan_year_end;
run;

/*Impute schedule B characteristics for form year 2008 by interpolating 2007 and 2009*/

data db_brx0709;
      set db_brx_fin0;
      where plan_year_end in (2007,2009);
run;

proc sort data = db_brx0709;
      by opr_ein opr_pn plan_year_end;
run;

proc means noprint data = db_brx0709;
      by opr_ein opr_pn;
      output out = actrl_imp_08
      mean(plan_assets) = imp08_plan_assets
      mean(plan_liab) = imp08_plan_liab
      mean(actives_liab) = imp08_actives_liab
      mean(rtd_liab) = imp08_rtd_liab
      mean(xpct_liab_incr) = imp08_xpct_liab_incr
      mean(actives_cnt) = imp08_actives_cnt
      mean(rtd_cnt) = imp08_rtd_cnt
      mean(term_cnt) = imp08_term_cnt
      mean(plan_R_age) = imp08_plan_R_age;
run;

data actrl_imp_08;
      set actrl_imp_08;
      year=2008;
run;

proc sql;
      create table db_brx_fin as 
      select a.*, 
      b.imp08_plan_assets, b.imp08_plan_liab, b.imp08_actives_liab,
      b.imp08_rtd_liab, b.imp08_xpct_liab_incr, b.imp08_actives_cnt,
      b.imp08_rtd_cnt, b.imp08_term_cnt, b.imp08_plan_R_age
      from db_brx_fin0 as a left join
      actrl_imp_08 as b on
      a.opr_ein = b.opr_ein & a.opr_pn = b.opr_pn & a.plan_year_end = b.year;
quit;

data db_brx_fin (drop = imp08_plan_assets imp08_plan_liab imp08_actives_liab
                 imp08_rtd_liab imp08_xpct_liab_incr imp08_actives_cnt
                 imp08_rtd_cnt imp08_term_cnt imp08_plan_R_age);
      set db_brx_fin;
      if plan_year_end = 2008 & plan_assets = . then plan_assets = imp08_plan_assets;
      if plan_year_end = 2008 & plan_liab = . then plan_liab = imp08_plan_liab;
      if plan_year_end = 2008 & actives_liab = . then actives_liab = imp08_actives_liab;
      if plan_year_end = 2008 & rtd_liab = . then rtd_liab = imp08_rtd_liab;
      if plan_year_end = 2008 & xpct_liab_incr = . then xpct_liab_incr = imp08_xpct_liab_incr;      
      if plan_year_end = 2008 & actives_cnt = . then actives_cnt = round(imp08_actives_cnt);    
      if plan_year_end = 2008 & rtd_cnt = . then rtd_cnt = round(imp08_rtd_cnt);   
      if plan_year_end = 2008 & term_cnt = . then term_cnt = round(imp08_term_cnt); 
      if plan_year_end = 2008 & plan_R_age = . then plan_R_age = round(imp08_plan_R_age);
run; 

/*Create a latent firm identifier -- the first firm ID associated with a plan
* The firm may switch from MU to SU or vice versa without losing its latent
* firm id. This will be what the long-term analysis will be based on*/

proc sort data = db_brx_fin;
  by opr_ein opr_pn plan_year_end;
run;

data firstid (keep = opr_ein opr_pn plan_year_end firmid first_file_flag rename=(firmid=first_firmid));
  set db_brx_fin;
  first_id+1;
  by opr_ein opr_pn;
  if first.opr_ein or first.opr_pn then first_file_flag=1;
  if first_file_flag^=1 then delete;
run;

proc sql;
    create table db_brx_fin1 as
    select a.*, b.first_firmid
    from db_brx_fin as a left join
    firstid as b 	
    on a.opr_ein = b.opr_ein & a.opr_pn = b.opr_pn;
quit;

/*Count the number of plans per year per firm and the number of frozen plans per year per first firm id*/

data db_brx_fin1;
	set db_brx_fin1;
	t=1;
	frz_t = t*frozen;
	cbal_t = t*cash_bal;
	frz_or_cb = max(frozen,cash_bal);
	frz_or_cb_t = t*frz_or_cb;
run;

proc sort data = db_brx_fin1;
	by first_firmid plan_year_end;
run;

proc means noprint data = db_brx_fin1;
	by first_firmid plan_year_end;
	output out = dbpanel_stats1 
	sum(t) = db_cnt
	sum(frz_t) = frozen_cnt
	sum(cbal_t) = cash_bal_cnt
	sum(frz_or_cb_t) = frz_cb_cnt;
run;

proc sql;
	create table db_brx_fin2 as
	select a.*, b.db_cnt, b.frozen_cnt, b.cash_bal_cnt, b.frz_cb_cnt
	from db_brx_fin1 as a inner join
	dbpanel_stats1 as b on 
	a.first_firmid = b.first_firmid & a.plan_year_end = b.plan_year_end;
quit;	

/*For each firmid count the max number of DB plans over the duration of the panel
 (=1 for firms that never have more than 1 plan)*/
proc sort data = db_brx_fin2;
	by first_firmid;
run;

proc means noprint data = db_brx_fin2;
	by first_firmid;
	output out = max_db_cnt
	max(db_cnt) = max_db_cnt
	min(db_cnt) = min_db_cnt;
run;

proc sql;
	create table db_brx_fin3 as 
	select a.*, b.max_db_cnt, b.min_db_cnt
	from db_brx_fin2 as a inner join
	max_db_cnt as b on 
	a.first_firmid = b.first_firmid;
quit;


/*Collapse by plan to obtain start year of freeze/CB and st/end years of visibility in f5500*/
proc sort data = db_brx_fin3;
	by opr_ein opr_pn plan_year_end;
run;

proc means noprint data = db_brx_fin3;
	by opr_ein opr_pn;
	output out = dbpanel_stats2 
	min(yr_freeze) = yr_first_frozen
	max(yr_freeze) = yr_last_frozen
	min(yr_cash_bal) = yr_first_cb
	max(yr_cash_bal) = yr_last_cb
	max(plan_year_end) = lst_planyr_observed
	min(form_year) = fyr_first_observed
	max(form_year) = fyr_last_observed;
run;

data dbpanel_stats2;
	set dbpanel_stats2 (drop = _type_ _freq_) ;
	if yr_first_frozen>0 then ever_frozen = 1;
	else ever_frozen=0;
	if yr_first_cb>0 then ever_cb = 1;
	else ever_cb=0;
run;

/*Merge the collapsed data back with the panel*/
proc sql;
	create table db_brx_fin4 as
	select *
	from db_brx_fin3 as a inner join
	dbpanel_stats2 as b 
	on a.opr_ein = b.opr_ein & a.opr_pn = b.opr_pn;
quit;

/*Flag firms that either freeze or cash balance convert 
all plans at once or never freeze/cash balance convert*/
data temp;
	set dbpanel_stats1 (drop = _type_ _freq_);
	helper=0;
	if /*redacted*/ OR /*redacted*/ then helper=1;
run;

proc means noprint data = temp;
	by first_firmid;
	output out = dbpanel_stats3
	min(helper) = in_sample;
run;

/*Merge back with the panel*/
proc sql;
	create table db_brx_fin5 as 
	select a.*, b.in_sample
	from db_brx_fin4 as a inner join
	dbpanel_stats3 as b 
	on a.first_firmid=b.first_firmid;
quit;

/*Use non-missing longitudinal reports to impute plan reported R age when it is missing*/
proc sort data = db_brx_fin5;
	by opr_ein opr_pn;	
run;

proc means noprint data = db_brx_fin5;
	by opr_ein opr_pn;	
	output out = av_R_age
	mean(plan_R_age) = av_R_age_nm;
run;

proc sql;
	create table db_brx_fin6 as 
	select a.*, b.av_R_age_nm
	from db_brx_fin5 as a left join
	av_R_age as b on
	a.opr_ein = b.opr_ein & a.opr_pn = b.opr_pn ;
quit;

data db_brx_fin6;
	set db_brx_fin6;
	plan_R_age_imputed = 0;
	if plan_R_age=. & av_R_age_nm^=. then plan_R_age_imputed=1;
	if plan_R_age=. then plan_R_age=round(av_R_age_nm) ;
run;

/*Save the panel and label variables*/

data blue.db_brx_panel (drop = t frz_t cbal_t frz_or_cb_t yr_freeze yr_cash_bal);
	set db_brx_fin6;
	label 
	first_firmid = "First firmid associated with EIN-PN"
	max_db_cnt = "Max number of plans associated with this firmid in all years"
	db_cnt = "Number of plans associated with this firmid for this plan year"
	frozen_cnt = "Number of plans associated with this firmid-planyear that are frozen"
	in_sample = "Flag for firms that have 1 plan or freeze/CB their multiple plans in a single year"
	yr_first_frozen = "First year in which plan is observed as frozen"
	yr_last_frozen = "Last year in which plan is observed as frozen"
	yr_first_cb = "First year in which plan is observed as CB"
	yr_last_cb = "Last year in which plan is observed as CB"
	ever_frozen = "Flag for plans that are ever frozen"
	ever_cb = "Flag for plans that are ever CB"
	active = "Flag for active record from BR"
	flag = "Type of BR match"
	ein5500 = "Plan sponsor EIN from BR-5500 xwalk file"
	fyr_first_observed = "Form year first observed"
	fyr_last_observed = "Form year last observed"
	lst_planyr_observed = "Last plan year observed"
	actives_cnt = "Active ptcp from Sch B/SB"
	actives_liab = "Active ptcp liability"
	plan_R_age = "Plan retirement age from Sch B/SB"
	plan_R_age_imputed = "Flag for plan retirement imputed as average of non-missing values"
	plan_assets = "Market value of plan assets"
	plan_liab = "Actuarial value of pension liability"
	rtd_cnt = "Retired ptcp from Sch B/SB"
	rtd_liab = "Retired ptcp liability"
	term_cnt = "Terminated ptcp from Sch B/SB"
	xpct_liab_incr = "Increase in liability due to service this year"
	esize_br = "Employer size from BR"
	epayroll_br = "Employer payroll from BR"
	cov_rate_br_adj = "DB coverage rate based on adjusted active participant count"
	cov_rate_br = "DB coverage rate based on unadjusted active participant count";
run;

/*first_firmid - firmid cross walk*/
proc sort data = db_brx_fin6 nodupkey out=firstid_xwlk (keep = firmid first_firmid plan_year_end);
  by firmid plan_year_end ;
run;

/*Collapse to the firmid level*/

/*First calculate coverage at the firm level*/
proc sort data = blue.db_brx_panel;
by firmid plan_year_end;
run;

proc means noprint data = blue.db_brx_panel;
	by firmid plan_year_end;
	output out = agg_coverage
	sum(adj_DB_partcp) = firmwide_adj_partcp;
run;

proc sql; 
	create table db_brx_panel_1 as 
	select a.*, b.firmwide_adj_partcp
	from blue.db_brx_panel as a inner join
	agg_coverage as b
	on a.firmid=b.firmid & a.plan_year_end = b.plan_year_end;
quit;

/*Restrict to firms that are single plan firms or freeze/db their plans 
* all at once or never*/
data blue.db_brx_panel_m;
	set db_brx_panel_1;
	label 
	firmwide_adj_partcp = "Sum of adj active ptcp across plans"
	cov_rate_firmwide = "DB coverage rate firmwide";
	/*keep firms that have a consistent number of plans each period*/
	where in_sample=1 & max_db_cnt = min_db_cnt; 
	cov_rate_firmwide=.;
	if size>0 then cov_rate_firmwide = firmwide_adj_partcp/size;
run;

/*Collapse to first_fimid-plan year end level*/
proc sort data = blue.db_brx_panel_m;
  by first_firmid plan_year_end;
run;

proc means noprint data = blue.db_brx_panel_m;
	by first_firmid plan_year_end;
	output out = unwtd_stats_m
	max(union_plan) = union_plan
	sum(union_plan) = num_union_plans
	mean(db_cnt) = db_cnt
	mean(frozen_cnt) = frozen_cnt
	mean(cash_bal_cnt) = cash_bal_cnt
	sum(adj_DB_partcp) = firmwide_adj_partcp
	sum(DB_partcp) = firmwide_partcp
	sum(actives_cnt) = firmwide_actives_schb
	sum(rtd_cnt) = firmwide_rtd_cnt
	sum(term_cnt) = firmwide_term_cnt
	sum(tot_contrib_amt_n) = tot_db_contrib
	sum(tot_income_amt_n) = tot_db_income
	sum(db_emplee_contrib) = tot_db_emplee_contrib
	sum(db_emplr_contrib) = tot_db_emplr_contrib
	max(cash_bal) = cash_bal
	max(frozen) = frozen
	sum(plan_assets) = plan_assets
	sum(plan_liab) = plan_liab
	sum(actives_liab) = actives_liab
	sum(rtd_liab) = rtd_liab
	sum(xpct_liab_incr) = xpct_liab_incr
	mean(yr_first_frozen) = yr_first_frozen
	mean(yr_last_frozen) = yr_last_frozen
	mean(yr_first_cb) = yr_first_cb
	mean(yr_last_cb) = yr_last_cb
	mean(plan_year_strt) = plan_year_strt
	max(ever_frozen) = ever_frozen
	max(ever_cb) = ever_cb
	mean(active) = active
	mean(mu) = mu
	mean(gov) = gov
	mean(flag) = flag
	mean(esize_br) = esize_br
	mean(epayroll_br) = epayroll_br
	mean(firmage) = firmage
	mean(cov_rate_firmwide) = cov_rate_firmwide
	mean(size) = size
	mean(lbd_pay) = lbd_pay
	mean(lbd_emp) = lbd_emp;
run;

data unwtd_stats_m;
	set unwtd_stats_m (drop=_type_ rename = (_freq_ = num_collapsed_plans));
	label 
	db_cnt = "Number of plans associated with this firmid for this plan start year"
	frozen_cnt = "Number of plans associated with this firmid for this plan start year that are frozen"
	cash_bal_cnt = "Number of plans associated with this firmid for this plan start year that are CB"
	yr_first_frozen = "First year in which plan is observed as frozen"
	yr_last_frozen = "Last year in which plan is observed as frozen"
	yr_first_cb = "First year in which plan is observed as CB"
	yr_last_cb = "Last year in which plan is observed as CB"
	ever_frozen = "Flag for plans that are ever frozen"
	ever_cb = "Flag for plans that are ever CB"
	active = "Flag for active record from BR"
	flag = "Type of BR record"
	firmwide_actives_schb = "Firmwide active ptcp from Sch B/SB"
	actives_liab = "Firmwide active ptcp liability"
	plan_assets = "Firmwide market value of plan assets"
	plan_liab = "Firmwide actuarial value of pension liability"
	firmwide_rtd_cnt = "Firmwide retired ptcp from Sch B/SB"
	rtd_liab = "Firmwide retired ptcp liability"
	firmwide_term_cnt = "Firmwide terminated ptcp from Sch B/SB"
	xpct_liab_incr = "Firmwide increase in liability due to service this year"
	esize_br = "Employer size from BR"
	epayroll_br = "Employer payroll from BR"
	firmage = "Firm age from LBD"
	lbd_pay = "Employer payroll from LBD"
	lbd_emp = "Employer size from LBD"
	size = "Best size LBD/BR"
	cov_rate_firmwide = "Firmwide coverage rate based on adjusted active participant count"
	union_plan = "At least one plan is a union plan"
	num_union_plans = "Number of union plans at the firm"
	firmwide_adj_partcp = "Firmwide number of active DB plan participants (adjusted)"
	firmwide_partcp = "Firmwide number of active DB plan participants (unadjusted)"
	num_collapsed_plans = "Number of plans collapsed for averaging"
	tot_db_contrib = "Total DB contributions"
	tot_db_income = "Total DB income"
	tot_db_emplee_contrib = "Total DB employee contributions"
	tot_db_emplr_contrib = "Total DB employer contributions";
run;

data tempR;
      set blue.db_brx_panel_m (keep = first_firmid firmid opr_ein opr_pn plan_year_end plan_R_age plan_eff_year adj_DB_partcp);
      wtd_R_var = plan_R_age*adj_DB_partcp;
      wtd_plan_birth_year = plan_eff_year*adj_DB_partcp;
run;

proc means noprint data = tempR;
      by first_firmid plan_year_end;
      output out = tempR_2
      sum(wtd_R_var) = sum_R_wtd
      sum(wtd_plan_birth_year) = sum_birthyr_wtd
      sum(adj_DB_partcp) = sum_wts;
run;

data wtd_stats_m;
      set tempR_2;
      plan_R_age= .;
      if sum_wts>0 then plan_R_age = round(sum_R_wtd/sum_wts);
      plan_eff_year =.;
      if sum_wts>0 then plan_eff_year = round(sum_birthyr_wtd/sum_wts);
      label 
      plan_R_age = "Average retirement age across plans wtd by actives"
      plan_eff_year = "Average DB plan birth year (wtd by actives)";
run;


/*Merge the wtd and unwtd collapsed stats together*/

proc sql;
	create table db_brx_firmpanel_m0 as 
	select a.plan_R_age, a.plan_eff_year, b.*
	from wtd_stats_m as a inner join
	unwtd_stats_m as b on 
	a.first_firmid = b.first_firmid & a.plan_year_end = b.plan_year_end;
quit;

/*Add on firmid*/
proc sql;
	create table db_brx_firmpanel_m as 
	select a.*, b.firmid
	from db_brx_firmpanel_m0 as a left join
	firstid_xwlk as b 
	on a.first_firmid = b.first_firmid & a.plan_year_end = b.plan_year_end;
quit;

/*SIC codes*/
data firm_sic;
      set blue.db_brx_panel_m (keep = firmid sic_br sic_2dig_br);
run;

proc sort data = firm_sic nodupkey;
      by firmid;
run;

proc sql;
      create table blue.db_brx_firmpanel_m as 
      select a.*, b.sic_br, b.sic_2dig_br
      from db_brx_firmpanel_m as a left join
      firm_sic as b
      on a.firmid = b.firmid;
quit;

/*Eliminate cases without a valid firmid-first_firmid link*/

data blue.db_brx_firmpanel_m 
  (rename = (lbd_emp=lbd_emp_v0 lbd_pay=lbd_pay_v0 firmage = firmage_v0)); 
  set blue.db_brx_firmpanel_m;
  where firmid~="";
run;
	
/*Get all observations of firmsize and pay for these firms (not contigent on filing 5500)*/

/*Get all unique firm id's*/
proc sort data = blue.db_brx_firmpanel_m nodupkey out = db_brx_unqfid_m;
by firmid;
run;

data db_brx_unqfid_m;
	set db_brx_unqfid_m (keep = firmid);
run;

/*Merge with LBD and firmid level DC information by year*/
%macro lbdmerge(yyyy);

	   /*industry code by firmid*/
	  data firm_ind;
	    set lbd&yyyy..lbd&yyyy.c_c201600 (keep=firmid mfsic1 mfsic4 mnaics2 mnaics4);
	  run;

	  proc sort data=firm_ind nodupkey;
	  by firmid;
	  run;

	   proc sql;
		    create table firmszpay0_&yyyy as
		    select a.*, b.emp as lbd_emp, b.pay as lbd_pay, b.firmage, b.year
		    from db_brx_unqfid_m as a inner join
		    lbd&yyyy..firm_&yyyy._emp_c201600 as b 
		    on a.firmid = b.firmid;	 
	   quit;

	   proc sql;
		    create table firmszpay_&yyyy as
		    select a.*, b.mfsic1, b.mfsic4, b.mnaics2, b.mnaics4
		    from firmszpay0_&yyyy as a inner join
		    firm_ind as b 
		    on a.firmid = b.firmid;	 
	   quit;

	  proc datasets lib=work nolist;
	      delete firmszpay0_&yyyy firm_ind;
	  quit;
	  run;

%mend;

%lbdmerge(1996); %lbdmerge(1997); %lbdmerge(1998); %lbdmerge(1999);
%lbdmerge(2000); %lbdmerge(2001); %lbdmerge(2002); %lbdmerge(2003);
%lbdmerge(2004); %lbdmerge(2005); %lbdmerge(2006); %lbdmerge(2007);
%lbdmerge(2008); %lbdmerge(2009); %lbdmerge(2010); %lbdmerge(2011);
%lbdmerge(2012); %lbdmerge(2013); %lbdmerge(2014);

/*Stack annual files together*/
data firmszpay_m;
	set firmszpay_1996 firmszpay_1997 firmszpay_1998
	firmszpay_1999 firmszpay_2000 firmszpay_2001 firmszpay_2002
	firmszpay_2003 firmszpay_2004 firmszpay_2005 firmszpay_2006
        firmszpay_2007 firmszpay_2008 firmszpay_2009 firmszpay_2010
	firmszpay_2011 firmszpay_2012 firmszpay_2013 firmszpay_2014;
run;

/*Merge back with information on freezes*/
proc sql;
	create table blue.firmszpay_m as
	select a.*, b.*
	from firmszpay_m as a left join
	blue.db_brx_firmpanel_m as b 
	on a.firmid = b.firmid & a.year = b.plan_year_end;
quit;

data blue.firmszpay_m;
	set blue.firmszpay_m;
	_f5500_filed=0;
	if plan_year_end^=. then _f5500_filed=1;
run;

proc export data = blue.firmszpay_m
	outfile = "/.../data/firmszpay_m.csv"
	dbms = csv replace;
run;	